package com.yanyanr.yr.greendaosample;

import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

import com.yanyanr.yr.greendaosample.greendao.DbHelper;
import com.yanyanr.yr.greendaosample.greendao.bean.UserModel;
import com.yanyanr.yr.greendaosample.greendao.gen.UserModelDao;

import org.greenrobot.greendao.query.CountQuery;
import org.greenrobot.greendao.query.CursorQuery;
import org.greenrobot.greendao.query.DeleteQuery;
import org.greenrobot.greendao.query.Query;
import org.greenrobot.greendao.query.QueryBuilder;

import java.util.ArrayList;
import java.util.List;

import butterknife.BindView;
import butterknife.ButterKnife;
import butterknife.OnClick;


public class MainActivity extends AppCompatActivity {

    @BindView(R.id.db_begin)
    Button begin;
    @BindView(R.id.db_clear)
    Button clear;
    @BindView(R.id.db_conver)
    Button conver;
    @BindView(R.id.db_test)
    Button test;
    @BindView(R.id.db_result)
    TextView textView;

    StringBuilder sb;

    String DbNameA = "AAA.db";
    String DbNameB = "BBB.db";
    String CurrentDbName ;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        sb = new StringBuilder("等待测试。。。。"+"\n");
        ButterKnife.bind(this);

        //设置数据库名称
        DbHelper.setDbName(DbNameA);
        CurrentDbName = DbNameA;
        begin.setText("测试开始"+"--当前："+ DbHelper.getInstance().getDbInfo());


    }


    /**
     * 显示内容
     * @param str
     */
    void showOnScreen(String str){
        sb.append(str+"\n");
        textView.setText(sb.toString());
    }

    /**
     * 清空数据
     */
    @OnClick(R.id.db_clear)
    public void clear(){
        sb = new StringBuilder("");
        clearData();
    }

    /**
     * 切换数据库
     */
    @OnClick(R.id.db_conver)
    public void conver(){
        sb = new StringBuilder("");

        if(CurrentDbName.equals(DbNameA)){
            DbHelper.getInstance().shotdown();
            DbHelper.setDbName(DbNameB);
            CurrentDbName = DbNameB;
            begin.setText("测试开始"+"--当前："+ DbHelper.getInstance().getDbInfo());
        }
        else{
            DbHelper.getInstance().shotdown();
            DbHelper.setDbName(DbNameA);
            CurrentDbName = DbNameA;
            begin.setText("测试开始"+"--当前："+ DbHelper.getInstance().getDbInfo());
        }
    }

    /**
     * 开始运行测试步骤
     */
    @OnClick(R.id.db_begin)
    public void begin() {
        sb = new StringBuilder("");
        showOnScreen("测试开始");

        showOnScreen("执行插入两条");
        inset();
        inset();
        query();
        showOnScreen("执行更新一条");
        update();
        query();
//        showOnScreen("执行删除一条");
//        delete();
//        query();

    }

    /**
     * 单独测试SqlStatement
     */
    @OnClick(R.id.db_test)
    public void test(){
        sb = new StringBuilder("");
//        Query query = DbHelper.getInstance().getUserModelDao().queryBuilder()
//                .whereOr(UserModelDao.Properties.Description.eq("苦逼程序员"),UserModelDao.Properties.Description.eq("普通程序员"))
//                .build();

        QueryBuilder qb = DbHelper.getInstance().getUserModelDao().queryBuilder();
//        qb.where(UserModelDao.Properties.Name.eq("Ryan"),
//                qb.or(UserModelDao.Properties.Description.eq("普通程序员"),UserModelDao.Properties.Description.eq("苦逼程序员")),
//                qb.and(UserModelDao.Properties.Description.eq("程序员"),UserModelDao.Properties.Description.eq("员")));
        qb.where(UserModelDao.Properties.Name.eq("Ryan"))
                .whereOr(UserModelDao.Properties.Description.eq("普通程序员"),UserModelDao.Properties.Description.eq("苦逼程序员"))
                .where(UserModelDao.Properties.Description.eq("程员"),UserModelDao.Properties.Description.eq("员"));

        List<UserModel> list = qb.build().list();
        String result = "";
        for(UserModel model: list){
            result += model.toString()+"\n";
        }
        showOnScreen("单独测试-查询："+"\n"+ result);

    }

    /**
     * 插入一条数据
     */
    void inset(){
        DbHelper.getInstance().getUserModelDao().save(new UserModel("9527","Ryan","普通程序员"));
    }
    /**
     * 删除第一条数据
     */
    void delete(){
        List<UserModel> list= DbHelper.getInstance().getUserModelDao().loadAll();
        DbHelper.getInstance().getUserModelDao().deleteByKey( list.get(0).getId());
    }
    /**
     * 清空数据
     */
    void clearData(){
        DbHelper.getInstance().deleteAllTables();
        query();
    }

    /**
     * 更新第一条数据
     */
    void update(){
        List<UserModel> list= DbHelper.getInstance().getUserModelDao().loadAll();
        list.get(0).setDescription("苦逼程序员");
        DbHelper.getInstance().getUserModelDao().update(list.get(0));
    }
    /**
     * 查询所有数据
     */
    void query(){

        String result = "";
        List<UserModel> list= DbHelper.getInstance().getUserModelDao().loadAll();

        for(UserModel model: list){
            result += model.toString()+"\n";
        }
        showOnScreen("查询所有数据："+"\n"+ result);
    }


    /**
     * 下面为常用方法列举
     */
    void sample(){
        UserModel user = new UserModel();
        List<UserModel> list = new ArrayList<>();
        ArrayList<String> arrayList = new ArrayList<>();
        UserModelDao UserDao =  DbHelper.getInstance().getUserModelDao();

        /**
         * 增加数据
         */
        UserDao.insert(user);//插入一行数据
        UserDao.insertInTx(user,user);//插入多行数据
        UserDao.insertInTx(list);//插入多行数据
        UserDao.save(user); //不存在插入，存在替换，也能穿多个或集合

        /**
         * 删除数据
         */
        UserDao.deleteByKey(21L); //根据主键删除
        UserDao.delete(user); // 删除行
        UserDao.deleteAll();//清空数据
        //根据查询条件删除例子在下面查询区

        /**
         * 更新数据
         */
        UserDao.update(user); //更新一行
        UserDao.updateInTx(user,user); //更新多个
        UserDao.updateInTx(list); //更新集合

        /**
         * 查询方法
         */
        UserDao.count();//总数据条数
        list= UserDao.loadAll();//获取所有数据

        //单个条件查询
        Query query = UserDao.queryBuilder()
                .where(UserModelDao.Properties.Name.eq("Ryan")) //等于
                .where(UserModelDao.Properties.Name.notEq("Ryan")) //不等于
                //多个where相当于and
                .where(UserModelDao.Properties.Name.between("123","543"))//选取介于两个值之间的数据范围
                .where(UserModelDao.Properties.Name.gt("Ryan")) // 大于
                .where(UserModelDao.Properties.Name.ge("Ryan")) // 大于等于
                .where(UserModelDao.Properties.Name.lt("Ryan")) // 小于
                .where(UserModelDao.Properties.Name.le("Ryan")) // 小于等于
                .where(UserModelDao.Properties.Name.in("Ryan","hr","br")) //多个值其中
                .where(UserModelDao.Properties.Name.in(arrayList)) //多个值其中
                .where(UserModelDao.Properties.Name.notIn("Ryan")) //不在多个值其中
                .where(UserModelDao.Properties.Name.like("Ryan")) //"%" 定义通配符模糊查询
                .where(UserModelDao.Properties.Name.isNotNull()) //不为空
                .where(UserModelDao.Properties.Name.isNull()) //为空
                .build();


        list = query.list();

        //query控制返回结果
        query.setLimit(20); //只返回20个结果
        query.setOffset(20);//偏移量，从20开始返回

        //多个条件查询 and
        query = UserDao.queryBuilder()
                .where(UserModelDao.Properties.Name.eq("Ryan"),
                        UserModelDao.Properties.Description.notEq("Ryan"),
                        UserModelDao.Properties.Id.notEq("3"))
                .build();

        list = query.list();

        //多个条件查询 或 or
        query = UserDao.queryBuilder()
                .whereOr(UserModelDao.Properties.Name.eq("Ryan"),
                        UserModelDao.Properties.Description.notEq("Ryan"),
                        UserModelDao.Properties.Id.notEq("3"))
                .build();

        list = query.list();


        //条件and() or()
        QueryBuilder qb = UserDao.queryBuilder();
        qb.where(UserModelDao.Properties.Name.eq("Ryan"),
                qb.or(UserModelDao.Properties.Description.eq("普通程序员"),UserModelDao.Properties.Description.eq("苦逼程序员")),
                qb.and(UserModelDao.Properties.Description.eq("程序员"),UserModelDao.Properties.Description.eq("员")));

        list = qb.list();

        //只返回查询结果数量
        CountQuery countQuery = UserDao.queryBuilder()
                .where(UserModelDao.Properties.Name.eq("Ryan"))
                .buildCount();

        long count = countQuery.count();

        //返回Cursor
        CursorQuery cursorQuery = UserDao.queryBuilder()
                .where(UserModelDao.Properties.Name.eq("Ryan"))
                .buildCursor();

        Cursor cursor=  cursorQuery.query();

        //删除查询的结果
        DeleteQuery deleteQuery = UserDao.queryBuilder()
                .where(UserModelDao.Properties.Name.eq("Ryan"))
                .buildDelete();

        deleteQuery.executeDeleteWithoutDetachingEntities();

        //其他特殊查询条件
        query = UserDao.queryBuilder()
                .where(UserModelDao.Properties.Name.eq("Ryan"))
                .orderAsc(UserModelDao.Properties.Id) // 升序
                .orderDesc(UserModelDao.Properties.Id) //降序
                .orderRaw("  ") //原生SQL语句条件排序
                .distinct() //过滤掉重复，返回唯一的
                .limit(20) //限制结果数量
                .build();

        list = query.list();


        //join 多表联查
//        QueryBuilder<UserModel> queryBuilder = UserDao.queryBuilder();
//        queryBuilder.join(AddressModel.class, AddressModelDao.Properties.UserID)
//                .where(AddressModelDao.Properties.Street.eq("Sesame Street"));
//        list = queryBuilder.list();﻿


        /**
         * 原生语句
         */
        list = UserDao.queryRaw("where name = ?" ,"Ryan"); // select已经默认添加并选择所有
        Query querRyanaw = UserDao.queryRawCreate("where name = ?" ,"Ryan");//同上，只是Query封装了结果

        UserDao.getDatabase().execSQL(" SqlStatements ",new String[]{"",""}); //原生语句
        cursor = UserDao.getDatabase().rawQuery(" SqlStatements ",new String[]{"",""});//原生查询

    }

}

//        单条件查询
//        SELECT T."_id",T."NAME",T."DESCRIPTION" FROM "USER_MODEL" T  WHERE T."NAME"=?
//
//        连续拼接where
//        SELECT T."_id",T."NAME",T."DESCRIPTION" FROM "USER_MODEL" T  WHERE T."NAME"=? AND T."DESCRIPTION"=?
//        whereOr
//        SELECT T."_id",T."NAME",T."DESCRIPTION" FROM "USER_MODEL" T  WHERE (T."DESCRIPTION"=? OR T."DESCRIPTION"=?)
//        and()or() -- 注意括号
//        SELECT T."_id",T."NAME",T."DESCRIPTION" FROM "USER_MODEL" T  WHERE T."NAME"=? AND (T."DESCRIPTION"=? OR T."DESCRIPTION"=?) AND (T."DESCRIPTION"=? AND T."DESCRIPTION"=?)
//        where()whereOr() --注意括号
//        SELECT T."_id",T."NAME",T."DESCRIPTION" FROM "USER_MODEL" T  WHERE T."NAME"=? AND (T."DESCRIPTION"=? OR T."DESCRIPTION"=?) AND T."DESCRIPTION"=? AND T."DESCRIPTION"=?
